package com.cfs.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.util.UUID;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import lombok.val;

import org.apache.xmlbeans.impl.xb.xsdschema.Public;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;




import com.cfs.service.CfsServiceImpl;
import com.cfs.service.CfsServiceImpl.XiaLa;
import com.shuohe.service.EasyUiDTO;
import com.shuohe.service.util.sql.EasyuiServiceImpl;
import com.shuohe.util.db.DbQuerySql;
import com.shuohe.util.easyuiBean.EasyUiDataGrid;
import com.shuohe.util.json.Json;

/**
 * 自定义表单数据操作
 * @author zz
 *
 */
@Controller  
@RequestMapping("/crm/ActionFormUtil/*")  
public class ActionFormUtil
{
	
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
    @Resource
    private EasyuiServiceImpl easyUiDto;
    
    @Resource
    private CfsServiceImpl cfsServiceImpl;
	
	private void debug(String s)
	{
		System.out.println(s);
	}
	/**
	 * 根据表名新增数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="insert.do")  
    public @ResponseBody int insert(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String jsonStr = request.getParameter("jsonStr");
		
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title");
		JSONArray arrayFiled  = obj.getJSONArray("field");
		List<allTablePro> listEnTable = new ArrayList<allTablePro>();
		String uuid = "";
		if(arrayFiled.length()>0){
			
			JSONObject  o0 =arrayFiled.getJSONObject(0);
			String key = o0.getString("text");//拼接的字段	
			String val =o0.getString("value");//拼接的值
			
			List<allTablePro> temp1 = getTempListTable(key);
			System.err.println("temp1_"+temp1.size());
			if(temp1 != null && temp1.size() > 0){
				for (allTablePro a : temp1) {
					listEnTable.add(a);
				}
			}
			 
			for(int i=1;i<arrayFiled.length();i++){
				JSONObject  o =arrayFiled.getJSONObject(i);
				String filed = ","+o.getString("text");
				String value = ","+o.getString("value");
				/*if("uuid".equals(o.getString("text"))) {
					uuid = o.getString("value");	//前台需要返回uuid		
				}*/
				key  +=filed ;
				val  +=value ;
				List<allTablePro> temp2 = getTempListTable(o.getString("text"));
				if(temp2 != null && temp2.size() > 0){
					for (allTablePro b : temp2) {
						listEnTable.add(b);
					}
				}
			}
			for (allTablePro tempTable : listEnTable) {
				System.err.println(tempTable.getName()+"___"+tempTable.getText()+"___"+tempTable.getText_en());
				for(int i=0;i<arrayFiled.length();i++){
					JSONObject  o =arrayFiled.getJSONObject(i);
					
					if(o.getString("text").equals(tempTable.getName()) && o.getString("value").equals("'"+tempTable.getText()+"'")){
						String filed = o.getString("text")+"_en";
						String value = "'"+tempTable.getText_en()+"'";
						//str  +=filed+"="+value;
						key  +=","+filed;
						val  +=","+value;
					}
				}
			}
			String sql = "insert into "+tableName+" ("+key+") values ("+val+")";
			System.out.println("sql=="+sql);
		 	int i=0; 
		 	Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
            i = stmt.executeUpdate(sql); 
			/*String str = uuid+","+i ;
			
			System.out.println("sql=="+str);*/
            stmt.close();
			return 1;

		}else{
			return -1;
		}
	}
	/**
	 * 根据id,修改数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="update.do")  
    public @ResponseBody int update(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		String jsonStr = request.getParameter("jsonStr");
		
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title");
		JSONArray arrayFiled  = obj.getJSONArray("field");
		
		List<allTablePro> listEnTable = new ArrayList<allTablePro>();
		
		if(arrayFiled.length()>0){
			
			JSONObject  o0 =arrayFiled.getJSONObject(0);
			String filed0 = o0.getString("text");
			String value0 = o0.getString("value");
			String str =filed0+ "="+value0;//拼接的字段	
			
			List<allTablePro> temp1 = getTempListTable(filed0);
			//System.err.println("temp1_"+temp1.size());
			if(temp1 != null && temp1.size() > 0){
				for (allTablePro a : temp1) {
					listEnTable.add(a);
				}
			}
			for(int i=1;i<arrayFiled.length();i++){
				JSONObject  o =arrayFiled.getJSONObject(i);
				String filed = ","+o.getString("text");
				String value = o.getString("value");
				str  +=filed+"="+value;				
				List<allTablePro> temp2 = getTempListTable(o.getString("text"));
				if(temp2 != null && temp2.size() > 0){
					for (allTablePro b : temp2) {
						listEnTable.add(b);
					}
				}
			}
			for (allTablePro tempTable : listEnTable) {
				System.err.println(tempTable.getName()+"___"+tempTable.getText()+"___"+tempTable.getText_en());
				for(int i=0;i<arrayFiled.length();i++){
					JSONObject  o =arrayFiled.getJSONObject(i);
					
					if(o.getString("text").equals(tempTable.getName()) && o.getString("value").equals("'"+tempTable.getText()+"'")){
						String filed = ","+o.getString("text")+"_en";
						String value = "'"+tempTable.getText_en()+"'";
						str  +=filed+"="+value;
					}
				}
			}
			System.out.println("循环外str:"+str);
			String sql = "update "+tableName+" set "+str+"  where id="+id;
			System.out.println("sql==="+sql);
		 
		 	int i=0; 
		 	Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
            i = stmt.executeUpdate(sql); 
			System.out.println("i=="+i);
			stmt.close();
			return i;
		}else{
			return -1;
		}
	}
	/**
	 * 返回含英文字符的表
	 * @param tableName
	 * @return
	 * @throws Exception 
	 */
	public List<allTablePro> getTempListTable(String tableName) throws Exception{
		String stringArray[] = {"fengjileixing","xiuzhengyinsu","shifufangbao","fengtongbanhou","fengtongleixing","huanqileixing","fanghuwang","kongjianjiareqi","chuandongfangshi","jikebanhou","jikeleixing","jinkoufanghuwang","chukoufanghuwang","chanpinleixing"};
		if(!Arrays.asList(stringArray).contains(tableName)){
			return null;
		}		
		Map<String, String> map = getTableField();
		return getAllTableProList(map.get(tableName),tableName);
	}
	
	/**
	 * 临时转英文类
	 * @author XUEJUN
	 *
	 */
	public class allTablePro{
		String name;
		String text;
		String text_en;
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getText() {
			return text;
		}
		public void setText(String text) {
			this.text = text;
		}
		public String getText_en() {
			return text_en;
		}
		public void setText_en(String text_en) {
			this.text_en = text_en;
		}
	}
	/**
	 * 返回集合
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
   public List<allTablePro> getAllTableProList(String tableName,String typeName) throws Exception {
	   List<allTablePro> arr = new ArrayList<>();
        String sql = "select  text, text_en  from "+tableName;
        Connection conn = null;
		 Statement stmt = null;
	    try {
		     conn = jdbcTemplate.getDataSource().getConnection();
			 stmt = conn.createStatement();
			 ResultSet rs = stmt.executeQuery(sql);
			 
			 int rowCount = 0; 
			 while(rs.next()) {
				allTablePro a = new allTablePro();
	    		a.setName(typeName); 
	    		a.setText(rs.getString("text"));
	    		a.setText_en(rs.getString("text_en"));
	    		arr.add(a);
	    		rowCount++;
			 }
			 System.err.println(rowCount);
			 conn.close();
        } catch (SQLException e) {
			e.printStackTrace();
        }finally {
        	stmt.close();
	    	conn.close();
	    }
	    return arr ;
   }
   /**
    * 表名与下拉字段的对应关系
    * @return
    */
   public Map<String, String> getTableField(){
	   Map<String, String> m = new HashMap<String , String>(); 
	   m.put("fengjileixing", "select_fanTypeSelect");
	   m.put("xiuzhengyinsu", "select_reviseFactorSelect");
	   m.put("shifufangbao", "select_ifExplosionProofSelect");
	   m.put("fengtongbanhou", "select_airDuctPlySelect");
	   m.put("fengtongleixing", "select_airDuctTypeSelect");
	   m.put("huanqileixing", "select_aerationTypeSelect");
	   m.put("fanghuwang", "select_protectiveScreeningSelect");
	   m.put("kongjianjiareqi", "select_spaceHeaterSelect");
	   m.put("chuandongfangshi", "select_transmission");
	   m.put("jikebanhou", "select_shellthickness");
	   m.put("jikeleixing", "select_chassistype");
	   m.put("jinkoufanghuwang", "select_importprotection");
	   m.put("chukoufanghuwang", "select_exportprotection");
	   m.put("chanpinleixing", "select_producttype");
	   return m;
   }
	/** 
	 * 根据id,删除表中数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="delete.do")  
    public @ResponseBody int delete(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		String tableName = request.getParameter("tableName"); 
		int i=0; 
	    String sql = "delete from "+tableName+" where id="+Integer.parseInt(id);
	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql);  
        stmt.close();
	    return i;
	}
	/**
	 * 根据表名从table_manage删除表
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="deleteByTableName.do")  
    public @ResponseBody int deleteByTableName(HttpServletRequest request,HttpServletResponse response) throws Exception{  
    		String tableName = request.getParameter("tableName"); 
    		int i=0; 
    	    String sql = "delete from table_manage where title='"+tableName+"'";
    	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql);  	
        stmt.close();
        if(1==i) {
        	 dropTableByName(tableName); 
        }
	    return i; 		
	} 
	/**
	 * 根据表名从table_manage删除表
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public int  dropTableByName(String tableName) throws Exception {
		
	    int i=0; 
	    String sql = "drop table " + tableName;
	    System.out.println(sql);
	    PreparedStatement  preStmt =jdbcTemplate.getDataSource().getConnection().prepareStatement(sql);
        i=preStmt.executeUpdate();  
        System.out.println("创建副表成功："+i);
        preStmt.close();
	    return i;//返回影响的行数，1为执行成功  
	}
	
	
	/**
	 * 根据表名获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getByTableName.do")  
    public @ResponseBody EasyUiDataGrid getByTableName(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName"); 
		String create_user_id = request.getParameter("create_user_id");
		  
		String sql = "";
		sql += "SELECT * FROM  " + tableName+" order by id desc";
		//sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+" order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		String sql_count = "select  *  from   "+ tableName ; 
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	@RequestMapping(value="getByTableNameAndUserId.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndUserId(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String sql = "";
		//根据用户的id 取出对应的数据
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+" order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id ; 
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	@RequestMapping(value="getByTableNameAndUserIdAndPid.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndUserIdAndPid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String pid = request.getParameter("pid");
		String sql = "";
		//根据用户的id 取出对应的数据
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+" and pid='"+pid+"' order by id desc";
		System.out.println("sql："+sql);
		sql+=DbQuerySql.limit(page, rows);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id+" and pid='"+pid+"'" ; 
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 根据表名和搜索条件获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getByTableNameAndSearch.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndSearch(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String conditions = request.getParameter("conditions");
		  
		String sql = "";
		//sql += "SELECT * FROM  " + tableName+" where 1=1 "+conditions+" order by id desc";
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+conditions+" order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		System.out.println("sql="+sql);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id+conditions ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	@RequestMapping(value="getByTableNameAndSearchAndPid.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndSearchAndPid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String conditions = request.getParameter("conditions");
		String pid = request.getParameter("pid");
		  
		String sql = "";
		//sql += "SELECT * FROM  " + tableName+" where 1=1 "+conditions+" order by id desc";
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+conditions+" and pid='"+pid+"' order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		System.out.println("sql="+sql);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id+conditions+" and pid='"+pid+"'"; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 根据表名获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getDataByUuid.do")  
    public @ResponseBody EasyUiDataGrid getDataByUuid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String result ="";
		 
		String uuid = request.getParameter("uuid"); 
		String tableName = request.getParameter("tableName");
		  
		String sql = "select  *  from   " + tableName+"  where uuid='"+uuid+"'";
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql);
    		return l;
	}
	@RequestMapping(value="getDataById.do")  
    public @ResponseBody EasyUiDataGrid getDataById(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String result ="";
		 
		String id = request.getParameter("id"); 
		String tableName = request.getParameter("tableName");
		  
		String sql = "select  *  from   " + tableName+"  where id='"+id+"'";
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql);
    		return l;
	}
	/**
	 * 获取所有的表格
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getAllTable.do")  
    public @ResponseBody EasyUiDataGrid getAllTable(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		  
		String sql = "";
		sql += "SELECT * FROM table_manage";
		sql+=DbQuerySql.limit(page, rows);
		
		/*String sql_count = "select count(*) total from  table_manage " ;*/ 
		String sql_count = "select  *  from  table_manage " ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 根据type 获取所有的子表
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getChildTableByType.do")  
    public @ResponseBody EasyUiDataGrid getChildTableByType(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		  
		String sql = "";
		sql += "SELECT * FROM table_manage where type=1";
		sql+=DbQuerySql.limit(page, rows);
		
		/*String sql_count = "select count(*) total from  table_manage " ;*/ 
		String sql_count = "select  *  from  table_manage where type=1"  ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	
	/**
	 * 根据表名和父表uuid获取字表数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getSonDataByUuid.do")  
    public @ResponseBody EasyUiDataGrid getSonDataByUuid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName"); 
		String uuid = request.getParameter("uuid");
		  
		String sql = "";
		sql += "SELECT * FROM "+tableName+" where uuid='"+uuid+"'";
		sql+=DbQuerySql.limit(page, rows);
		
		String sql_count = "select  *  from  "+tableName+" where  uuid='"+uuid+"'";
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}

	@RequestMapping(value="getByType.do",produces="application/json; charset=UTF-8")  
    public @ResponseBody List<Map<String, Object>> getByType(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		String type = request.getParameter("type"); 
		List<Map<String, Object>> arr = cfsServiceImpl.getByType(type);
			return arr;
		 
	 }
	
}
